import datetime
from time import strftime, localtime

import connector.driver
from connector.driver import DatabaseDriver

# 借书时用户id和书id需要传入
def get_borrow_by_user_id_and_book_id(driver: DatabaseDriver, user_id: int, book_id: int)->tuple:
    return driver.select('select * from borrow where user_id={} and book_id={}'.format(user_id, book_id))

def get_borrow(driver: DatabaseDriver)->list:
    return driver.select('select * from borrow')

def get_borrow_by_user_id(driver: DatabaseDriver, user_id: int)->list:
    return driver.select('select * from borrow where user_id={}'.format(user_id))

def get_borrow_by_book_id(driver: DatabaseDriver, book_id: int)->list:
    return driver.select('select * from borrow where book_id={}'.format(book_id))

def update_borrow_return_time(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time= str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.update('update borrow set borrow_time="{}", return_time=null where user_id={} and book_id={}'.format(current_time, user_id, book_id))

def borrow(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time= str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.insert('insert into borrow(user_id, book_id, borrow_time) values ({}, {}, "{}")'.format(user_id, book_id, current_time))

def return_book(driver: DatabaseDriver, user_id: int, book_id: int)->int:
    current_time= str(strftime('%Y-%m-%d %H:%M:%S', localtime()))
    return driver.delete('delete from borrow where user_id={} and book_id={}'.format(user_id, book_id))
# 借阅书籍信息
def borrow_list(driver: DatabaseDriver, user_text: str=None, book_text: str=None, user_id: int=None, book_id: int=None)->list:
    if user_text is None and book_text is None and user_id is None and book_id is None:
        return driver.query_all('select user.user_id,user.user_name,book.book_id,book.book_name,borrow.borrow_time,borrow.return_time from user,borrow,book where borrow.user_id=user.user_id and borrow.book_id=book.book_id')
    else:
        if user_text is not None and book_text is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow.borrow_time,borrow.return_time from user,borrow,book where borrow.user_id=user.user_id and borrow.book_id=book.book_id and user.user_name like "{}%" and book.book_name like "{}%"'.format(user_text, book_text))
        elif user_text is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow.borrow_time,borrow.return_time from user,borrow,book where borrow.user_id=user.user_id and borrow.book_id=book.book_id and user.user_name like "{}%"'.format(user_text))
        elif book_text is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow.borrow_time,borrow.return_time from user,borrow,book where borrow.user_id=user.user_id and borrow.book_id=book.book_id and book.book_name like "{}%"'.format(book_text))
        elif user_id is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow.borrow_time,borrow.return_time from user,borrow,book where borrow.user_id=user.user_id and borrow.book_id=book.book_id and user.user_id={}'.format(user_text))
        elif user_id is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow_time,return_time from borrow,user,book where borrow.user_id = user.user_id and borrow.book_id = book.book_id and borrow.user_id = {}'.format(
                    user_id))
        elif book_id is not None:
            return driver.query_all(
                'select user.user_id,user.user_name,book.book_id,book.book_name,borrow_time,return_time from borrow,user,book where borrow.user_id = user.user_id and borrow.book_id = book.book_id and borrow.book_id = {}'.format(
                    book_id))
        else:
            return None

